﻿/*    ==脚本参数==

    源服务器版本 : SQL Server 2016 (13.0.1601)
    源数据库引擎版本 : Microsoft SQL Server Enterprise Edition
    源数据库引擎类型 : 独立的 SQL Server

    目标服务器版本 : SQL Server 2016
    目标数据库引擎版本 : Microsoft SQL Server Enterprise Edition
    目标数据库引擎类型 : 独立的 SQL Server
*/

USE [RossLive]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<chenkuan>
-- Description:	<自动刷新物料批次>
-- =============================================
CREATE procedure [dbo].[B_Auto_LotNum] 

as
--处理工单数据异常
delete jp
from live.erp.jobprod jp
left join Live.erp.jobhead jh on jh.company=jp.company and jh.jobnum=jp.jobnum
where jh.partnum<>jp.partnum 

--更新批次
update pl
set lotnum_c=
'S'+rtrim(ltrim(str(jp.OrderNum)))+'-'+rtrim(ltrim(str(jp.OrderLine)))+'-'+rtrim(ltrim(str(jp.OrderRelNum)))+
'P'+rtrim(ltrim(str(pl.ponum)))+'-'+rtrim(ltrim(str(pl.poline)))+'-'+rtrim(ltrim(str(pl.porelnum))) 
from Live.dbo.PORel pl
left join Live.erp.JobProd jp on pl.company=jp.company and pl.JobNum=jp.JobNum
where pl.TranType='pur-mtl' and pl.lotnum_c<>
'S'+rtrim(ltrim(str(jp.OrderNum)))+'-'+rtrim(ltrim(str(jp.OrderLine)))+'-'+rtrim(ltrim(str(jp.OrderRelNum)))+
'P'+rtrim(ltrim(str(pl.ponum)))+'-'+rtrim(ltrim(str(pl.poline)))+'-'+rtrim(ltrim(str(pl.porelnum))) 

--更新采购建议批次
update pl
set lotnum_c=isnull(od.POLine,'')+
'S'+rtrim(ltrim(str(jp.OrderNum)))+'-'+rtrim(ltrim(str(jp.OrderLine)))+'-'+rtrim(ltrim(str(jp.OrderRelNum)))
from Live.dbo.sugpodtl pl
left join Live.erp.JobProd jp on pl.company=jp.company and pl.JobNum=jp.JobNum
left join Live.erp.orderDtl od on jp.company=od.company and jp.OrderNum=od.OrderNum and jp.OrderLine=od.OrderLine
where pl.jobnum<>'' and pl.lotnum_c<>isnull(od.POLine,'')+
'S'+rtrim(ltrim(str(jp.OrderNum)))+'-'+rtrim(ltrim(str(jp.OrderLine)))+'-'+rtrim(ltrim(str(jp.OrderRelNum)))

--更新PartBin_UD表必要字段
update pb
set pb.Plant_c=w.Plant ,pb.NonNettable_c=b.NonNettable ,pb.type_c=t2.Type,
pb.Jobnum_c=t2.JobNum ,pb.Assemblyseq_c=t2.AssemblySeq,pb.MtlSeq_c=t2.JobSeq ,
pb.ponum_c=t2.PONum ,pb.poline_c=t2.POLine ,pb.porelnum_c=t2.PORelNum ,
pb.ordernum_c=t2.OrderNum ,pb.orderline_c=t2.OrderLine ,pb.orderrelnum_c=t2.OrderRelNum 
from Live.dbo.Partbin pb
left join(
select 
case 
when b.JobNum <>'0' THEN 'WSJ'
when b.OrderNum<>0 THEN 'WSB'
ELSE 'WSTK'
END 'Type',
b.* 
from(
select 
isnull(pr.PONum,0) 'PONum',isnull(pr.POLine,0) 'POLine',isnull(pr.PORelNum,0) 'PORelNum',
isnull(jm.JobNum,isnull(pr.JobNum,0)) 'JobNum',isnull(jm.AssemblySeq,ISNULL(pr.AssemblySeq,0)) 'AssemblySeq' ,isnull(jm.MtlSeq,isnull(pr.JobSeq,0)) 'JobSeq',
isnull(ol.OrderNum,0) 'OrderNum',isnull(ol.OrderLine,0) 'OrderLine',isnull(ol.OrderRelNum,0) 'OrderRelNum',
a.* 
from(
select 
case when pb.LotNum LIKE 'S%P%' THEN RIGHT(pb.LotNum ,LEN(pb.LotNum )-dbo.B_charindex(pb.LotNum,'P',1)+1) ELSE 'P0-0-0' END 'PO',
case when pb.LotNum LIKE 'S%J%' THEN RIGHT(pb.LotNum ,LEN(pb.LotNum )-dbo.B_charindex(pb.LotNum,'J',1)+1) ELSE 'J0-0-0' END 'JOB',
case 
when pb.LotNum LIKE 'S%P%' THEN LEFT(pb.LotNum ,dbo.B_charindex(pb.LotNum,'P',1)-1)
when pb.LotNum LIKE 'S%J%' THEN LEFT(pb.LotNum ,dbo.B_charindex(pb.LotNum,'J',1)-1)
when pb.LotNum LIKE 'S%' THEN pb.LotNum 
ELSE 'S0-0-0'
END 'SO',
pb.*
from Live.erp.PartBin pb
)a 
left join Live.erp.PORel pr on pr.Company=a.Company and a.PO='P'+rtrim(ltrim(str(pr.ponum)))+'-'+rtrim(ltrim(str(pr.poline)))+'-'+rtrim(ltrim(str(pr.porelnum)))
left join Live.erp.JobMtl jm on jm.Company=a.Company and a.JOB='J'+rtrim(ltrim(jm.JobNum))+'-'+rtrim(ltrim(str(jm.AssemblySeq)))+'-'+rtrim(ltrim(str(jm.MtlSeq)))
left join Live.erp.OrderRel ol on ol.Company=a.Company and a.SO='S'+rtrim(ltrim(str(ol.OrderNum)))+'-'+rtrim(ltrim(str(ol.OrderLine)))+'-'+rtrim(ltrim(str(ol.OrderRelNum)))
)b
)t2 on pb.Company=t2.Company and pb.PartNum=t2.PartNum and pb.WarehouseCode=t2.WarehouseCode and pb.BinNum=t2.BinNum and pb.OnhandQty=t2.OnhandQty and pb.LotNum=t2.LotNum
left join Live.erp.Warehse w on pb.company=w.company and w.WarehouseCode=pb.WarehouseCode
left join Live.erp.whsebin b on b.company=pb.company and b.WarehouseCode =pb.WarehouseCode and b.BinNum =pb.BinNum

GO

-- =============================================
-- Author:		<chenkuan>
-- Create date: <2018-3-29>
-- Description:	<同步Live库辅料编码>
-- =============================================
CREATE PROCEDURE [dbo].[Proc_CopyPartFL] 
	@Keywords nvarchar(50)='14%'
AS
BEGIN
	insert into [dbo].[EmpPartHits](PartNum,PartDesc,IUM,EmpID,JCDept,Hits,LimitQty,AprvGrade) 
	select part.PartNum,part.PartDescription,part.IUM,'manager' as EmpID,'ZP' as JCDept,
	0 as Hits,10 as LimitQty,1 as AprvGrade
	 from Live.Erp.Part part
	left join [dbo].[EmpPartHits] eph on eph.PartNum=part.PartNum
	where part.PartNum like '14%' and part.InActive=0 and eph.ID is null

	declare @PartNum nvarchar(50)
	declare @PartDescription nvarchar(500)
	declare @IUM nvarchar(20)

	DECLARE C_Part CURSOR FAST_FORWARD FOR
		select Part.PartNum,Part.IUM,Part.PartDescription from dbo.EmpPartHits eph
		inner join Live.Erp.Part on eph.PartNum=Part.PartNum
		where eph.PartDesc<>Part.PartDescription
	 OPEN C_Part

	FETCH NEXT FROM C_Part INTO @PartNum,@IUM,@PartDescription

	WHILE @@FETCH_STATUS=0
	BEGIN
		-- 操作
		UPDATE [dbo].[EmpPartHits] SET PartDesc=@PartDescription,IUM=@IUM 
		WHERE PartNum=@PartNum 

		-- 取下一条记录
		FETCH NEXT FROM C_Part INTO @PartNum,@IUM,@PartDescription
	END

	-- 关闭游标
	CLOSE C_Part;
	-- 释放游标
	DEALLOCATE C_Part;

END
GO

CREATE TABLE [dbo].[V_PART](
	[COMPANY] [nvarchar](255) NULL,
	[COSTMETHOD] [nvarchar](255) NULL,
	[PARTPLANT_PLANT] [nvarchar](255) NULL,
	[PARTPLANT_PRIMWHSE] [nvarchar](255) NULL,
	[PARTREV_REVISIONNUM] [nvarchar](255) NULL,
	[PARTREV_REVSHORTDESC] [nvarchar](255) NULL,
	[PARTREV_APPROVED] [nvarchar](255) NULL,
	[PARTREV_EFFECTIVEDATE] [nvarchar](255) NULL,
	[PARTNUM] [nvarchar](255) NULL,
	[IUM] [nvarchar](255) NULL,
	[PUM] [nvarchar](255) NULL,
	[SALESUM] [nvarchar](255) NULL,
	[UOMCLASSID] [nvarchar](255) NULL,
	[PARTDESCRIPTION] [nvarchar](255) NULL,
	[TYPECODE] [nvarchar](255) NULL,
	[CLASSID] [nvarchar](255) NULL,
	[TRACKLOTS] [nvarchar](255) NULL,
	[NONSTOCK] [nvarchar](255) NULL,
	[PARTPLANT_NONSTOCK] [nvarchar](255) NULL,
	[PARTPLANT_COSTMETHOD] [nvarchar](255) NULL,
	[PARTWHSE_WAREHOUSECODE] [nvarchar](255) NULL,
	[UTIME] [datetime] NULL,
	[CTIME] [datetime] NULL,
	[DATAFROM] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[V_NoReqMtlQry_1]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








CREATE VIEW [dbo].[V_NoReqMtlQry_1]
as

--已发放、未完结工单物料需求
with MtlReq as (
	select b.PartNum,b.Description as PartDescription,b.RequiredQty-b.IssuedQty as RequiredQty,a.JobNum,b.AssemblySeq,b.MtlSeq as JobSeq,'Job' as RType, 1 as  ReqReleased,a.Company
	from Live.Erp.JobHead a
	inner join Live.Erp.JobMtl b on a.Company=b.Company and a.JobNum=b.JobNum  and b.IssuedComplete=0
	where a.JobReleased=1 and a.JobClosed=0 and a.JobComplete=0
),
--未发放、未完结工单物料需求
MtlReqUn as (
	select b.PartNum,b.Description as PartDescription,b.RequiredQty-b.IssuedQty as RequiredQty,a.JobNum,b.AssemblySeq,b.MtlSeq as JobSeq,'Job' as RType, 0 as  ReqReleased,a.Company
	from Live.Erp.JobHead a
	inner join Live.Erp.JobMtl b on a.Company=b.Company and a.JobNum=b.JobNum  and b.IssuedComplete=0
	where a.JobReleased=0 and a.JobClosed=0
),
--安全库存
SaftyStock as (
	select a.PartNum,p.PartDescription,a.Quantity as RequiredQty,JobNum as JobNum, OrderLine as AssemblySeq,OrderRelNum as JobSeq,'Safty' as RType, FirmRelease as  ReqReleased,a.Company from Live.Erp.PartDtl a
	inner join Live.Erp.Part p on p.PartNum=a.PartNum and p.InActive=0
	where a.SourceFile='OR' and a.OrderNum=1
),
--直接采购SO
MtlSO as (
select	 	
	b.PartNum,
	b.LineDesc as PartDescription,
	c.SellingReqQty as RequiredQty,
	convert(nvarchar(20),a.OrderNum) as JobNum, b.OrderLine as AssemblySeq,c.OrderRelNum as JobSeq,
	'SO' as RType,
	c.OpenRelease as ReqReleased,
	a.Company
	from Live.Erp.OrderHed a
inner join Live.Erp.OrderDtl b on a.Company=b.Company and a.OrderNum=b.OrderNum and OpenLine=1
inner join Live.Erp.OrderRel c on c.Company=b.Company and c.OrderNum=b.OrderNum and c.OrderLine=b.OrderLine and c.DropShip=1 and OpenRelease=1
where a.OpenOrder=1
)
select * from MtlReq
--union all
--select * from MtlReqUn
union all
select * from MtlSO
union all
select * from SaftyStock


GO
/****** Object:  View [dbo].[V_RcvInspSearch]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [dbo].[V_RcvInspSearch]
AS
---待检物料查询
select 
	[RcvDtl].[PartNum] as [RcvDtl_PartNum],
	[RcvDtl].[PartDescription] as [RcvDtl_PartDescription],
	[RcvDtl].[PackSlip] as [RcvDtl_PackSlip],
	[RcvDtl].[PackLine] as [RcvDtl_PackLine],
	[RcvDtl].[OurQty] as [RcvDtl_OurQty],
	[RcvDtl].[VendorQty] as [RcvDtl_VendorQty],
	[RcvDtl].[IUM] as [RcvDtl_IUM],
	[RcvDtl].[JobNum] as [RcvDtl_JobNum],
	[RcvDtl].[AssemblySeq] as [RcvDtl_AssemblySeq],
	[RcvDtl].[JobSeq] as [RcvDtl_JobSeq],
	[RcvDtl].[JobSeqType] as [RcvDtl_JobSeqType],
	[RcvDtl].[PONum] as [RcvDtl_PONum],
	[RcvDtl].[POLine] as [RcvDtl_POLine],
	[RcvDtl].[PORelNum] as [RcvDtl_PORelNum],
	[RcvDtl].[WareHouseCode] as [RcvDtl_WareHouseCode],
	[RcvDtl].[BinNum] as [RcvDtl_BinNum],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[PORel].[TranType] as [PORel_TranType],
	[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
	[RcvDtl].[LotNum] as [RcvDtl_LotNum],
	[RcvDtl].[OurUnitCost] as [RcvDtl_OurUnitCost],
	[RcvDtl].[Company] as [RcvDtl_Company]
from Live.Erp.RcvDtl as RcvDtl
inner join Live.Erp.Vendor as Vendor on 
	RcvDtl.Company = Vendor.Company
And
	RcvDtl.VendorNum = Vendor.VendorNum

inner join Live.Erp.RcvHead as RcvHead on 
	RcvHead.Company = RcvDtl.Company
And
	RcvHead.VendorNum = RcvDtl.VendorNum
And
	RcvHead.PurPoint = RcvDtl.PurPoint
And
	RcvHead.PackSlip = RcvDtl.PackSlip
 and ( RcvHead.Plant = 'MfgSys'  )

inner join Live.Erp.PORel as PORel on 
	PORel.Company = RcvDtl.Company
And
	PORel.PONum = RcvDtl.PONum
And
	PORel.POLine = RcvDtl.POLine
And
	PORel.PORelNum = RcvDtl.PORelNum

 where (RcvDtl.InspectionPending = 1  and RcvDtl.ReceiptDate is not null and RcvDtl.Company = RcvHead.Company)

GO
/****** Object:  View [dbo].[V_NoReqMtlQry_2]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO













CREATE VIEW [dbo].[V_NoReqMtlQry_2]
as
---供应查询，库存+在途+收货+在检+DMR+工单+不合格
with MtlStk as (
	select a.PartNum,Part.PartDescription,a.OnhandQty as SupplyQty,a.PoNum_c as PONum,a.PoLine_c as POLine,a.PoRelNum_c as PORelNum,PartCost.AvgMaterialCost as DocUnitCost
	,a.OnhandQty*PartCost.AvgMaterialCost as MtlCost
	,a.WarehouseCode,a.BinNum,a.LotNum
	,a.JobNum_c as JobNum,a.AssemblySeq_c as AssemblySeq,a.MtlSeq_c as JobSeq,(case when a.AssemblySeq_c>0 then 'STOM' else 'STOS' end) as SType, 'Stock' as MtlS
	from Live.dbo.PartBin a
	inner join Live.Erp.Part on Part.Company=a.Company and Part.PartNum=a.PartNum
	left join Live.Erp.PartCost on PartCost.Company=a.Company and PartCost.PartNum=a.PartNum
	where a.WarehouseCode<>'DDWZ'
),
MtlSug as (
	select PartNum,LineDesc as PartDescription,RelQty as SupplyQty,PONUM,POLine,0 as PORelNum,0 as DocUnitCost  
	,'' as WarehouseCode,'' as BinNum,'' as LotNum
	,JobNum,AssemblySeq,JobSeq,
	(case when AssemblySeq>0 then 'STOM' else 'STOS' end) as SType, 'Sugg' as MtlS
	from Live.Erp.SugPoDtl
),
MtlInsp as (
	select 
	RcvDtl_PartNum as PartNum,
	RcvDtl_PartDescription as PartDescription,
	RcvDtl_OurQty as SupplyQty,
	RcvDtl_PONum as PONum,
	RcvDtl_POLine as POLine,
	RcvDtl_PORelNum as PORelNum,
	RcvDtl_OurUnitCost as DocUnitCost,
	RcvDtl_OurUnitCost*RcvDtl_OurQty as MtlCost
	,RcvDtl_WareHouseCode as WarehouseCode,RcvDtl_BinNum as BinNum,RcvDtl_LotNum as LotNum,
	RcvDtl_JobNum as  JobNum,
	RcvDtl_AssemblySeq as AssemblySeq,
	RcvDtl_JobSeq as JobSeq,
	(case when RcvDtl_AssemblySeq>0 then 'STOM' else 'STOS' end) as SType,
	'Insp' as MtlS
	from dbo.V_RcvInspSearch
),
MtlNonConf as (
	select 
	NonConf.PartNum,
	Part.PartDescription,
	Quantity as  SupplyQty,
	PONum,POLine,PORelNum,
	PartCost.LastMaterialCost as DocUnitCost,
	PartCost.LastMaterialCost*Quantity as MtlCost,
	WarehouseCode,BinNum,LotNum,
	JobNum,AssemblySeq,MtlSeq as JobSeq,
	'STOS' as SType,
	'NonConf' as MtlS
	from Live.Erp.NonConf
	left join Live.Erp.PartCost on PartCost.Company=NonConf.Company and PartCost.PartNum=NonConf.PartNum
	left join Live.Erp.Part on Part.Company=NonConf.Company and Part.PartNum=NonConf.PartNum
	where PassedQty=0 and FailedQty=0 and InspectionPending=1
),
MtlOnload as (
	select pd.PartNum,pd.LineDesc as PartDescription,pr.XRelQty-pr.ReceivedQty as SupplyQty,ph.PONum,pd.POLine,pr.PORelNum,pd.DocUnitCost
	,(pr.XRelQty-pr.ReceivedQty)*pd.DocUnitCost as MtlCost
	,'' as WarehouseCode,'' as BinNum,'' as LotNum
	,pr.JobNum,pr.AssemblySeq,pr.JobSeq,
	(case when pr.TranType='PUR-MTL' then 'STOM' else 'STOS' end) as SType, 'Onload' as MtlS
	from Live.Erp.PORel pr
	inner join Live.Erp.PODetail pd on pd.Company=pr.Company and pd.PONUM=pr.PONum and pd.POLine=pr.POLine and pd.OpenLine=1
	inner join Live.Erp.POHeader ph on ph.Company=pr.Company and ph.PONum=pr.PONum  and ph.BuyerID<>'PR012' and ph.OpenOrder=1
	where (pr.TranType='PUR-MTL' or pr.TranType='PUR-STK') and pr.XRelQty-pr.ReceivedQty>0
),
MtlDMR as (
	select 
		PartNum,
		PartDescription,
		TotDiscrepantQty-TotAcceptedQty-TotRejectedQty as SupplyQty,
		0 as PONum,0 as POLine,0 as PORelNum,
		AvgMtlUnitCost as DocUnitCost,
		AvgMtlUnitCost*(TotDiscrepantQty-TotAcceptedQty-TotRejectedQty) as MtlCost,
		WarehouseCode,
		BinNum,
		LotNum,
		JobNum,
		AssemblySeq,MtlSeq as JobSeq,
		(case when AssemblySeq>0 then 'STOM' else 'STOS' end) as SType,
		'DMR' as MtlS
	from Live.Erp.DMRHead where OpenDMR=1
),
MtlRcv as (
	select 
		RcvDtl.PartNum,
		RcvDtl.PartDescription,
		RcvDtl.OurQty as SupplyQty,
		RcvDtl.PONum,RcvDtl.POLine,RcvDtl.PORelNum,
		RcvDtl.DocUnitCost,
		RcvDtl.DocUnitCost*RcvDtl.OurQty as MtlCost,
		RcvDtl.WarehouseCode,
		RcvDtl.BinNum,
		RcvDtl.LotNum,
		RcvDtl.JobNum,
		RcvDtl.AssemblySeq,RcvDtl.JobSeq,
		(case when RcvDtl.AssemblySeq>0 then 'STOM' else 'STOS' end) as SType,
		'Rcv' as MtlS
	from Live.Erp.RcvHead 
	inner join Live.Erp.RcvDtl on RcvDtl.Company=RcvHead.Company and RcvDtl.PackSlip=RcvHead.PackSlip and RcvDtl.PONum=RcvHead.PONum and RcvDtl.Received=0
	where RcvHead.Received=0 --and RcvDtl.PartNum='121106-000478'
),
MtlJob as (
	select 
		a.PartNum,
		c.PartDescription,
		a.ProdQty as SupplyQty,
		0 as PONum,0 as POLine,0 as PORelNum,
		PartCost.AvgMaterialCost as DocUnitCost,
		PartCost.AvgMaterialCost * a.ProdQty as MtlCost,
		'' as WarehouseCode,'' as BinNum,'' as LotNum,
		a.JobNum,
		0 as AssemblySeq,
		0 as JobSeq,
		'STOS' as SType,
		'Job' as MtlS
		from Live.Erp.JobProd a
	inner join Live.Erp.JobHead b on b.JobNum=a.JobNum and b.JobComplete=0 and b.JobClosed=0  and b.JobEngineered=1
	inner join Live.Erp.Part c on c.PartNum=a.PartNum and c.TypeCode='P'
	left join Live.Erp.PartCost on PartCost.Company=a.Company and PartCost.PartNum=a.PartNum
)
select * from MtlOnload
union all
select * from MtlStk
union all
select * from MtlInsp
union all
select * from MtlNonConf
union all
select * from MtlDMR
union all
select * from MtlRcv
union all
select * from MtlJob
--union all
--select * from MtlSug

GO
/****** Object:  View [dbo].[V_NoReqMtlQry]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [dbo].[V_NoReqMtlQry]
AS
with ReqGroup as (
	select t.PartNum,SUM(t.RequiredQty) as RequiredQtySum from [dbo].[V_NoReqMtlQry_1] t group by t.PartNum
),
SuppGroup as (
	select t.PartNum,SUM(t.SupplyQty) as SupplyQtySum from [dbo].[V_NoReqMtlQry_2] t group by t.PartNum
),
UnitCost as (
	select * from (
	select Company,PartTran.PartNum,MtlUnitCost,TranDate,ROW_NUMBER() OVER(PARTITION BY PartTran.PartNum ORDER BY TranNum desc) AS ROWNUM from Live.Erp.PartTran 
	where MtlUnitCost>0 and TranType<>'ADJ-PUR'
	) t where t.ROWNUM=1
 )
select abc.*,abc.SQty*abc.MtlUnitCost as MtlCost
from (
	select tt.*,tt.SupplyQtySum-tt.RequiredQtySum as SQty,ISNULL(PartCost.AvgMaterialCost,0) as MtlUnitCost,Part.PartDescription from (
		select t.PartNum,t.SupplyQtySum,ISNULL(ReqGroup.RequiredQtySum,0) as RequiredQtySum from SuppGroup t
		left join ReqGroup on ReqGroup.PartNum=t.PartNum
	) tt
	left join Live.Erp.PartCost on PartCost.PartNum=tt.PartNum
	left join Live.Erp.Part on Part.PartNum=tt.PartNum
) abc

GO
/****** Object:  View [dbo].[V_NoReqMtlQry_3]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_NoReqMtlQry_3]
AS

select a.*,t.PartType,
(case when SUBSTRING(a.PartNum,1,6)='120800'  then N'减速机' else
	case when SUBSTRING(a.PartNum,1,6)='120700' and CHARINDEX(N'线性导轨',a.PartDescription)>0 then N'线性导轨' else
		case when SUBSTRING(a.PartNum,1,6)='120210' and CHARINDEX(N'铸件',a.PartDescription)>0 then N'铸件' else 
			case when SUBSTRING(a.PartNum,1,2)='13'  then N'材料' else '' end
		end
	end
end) as PartClass
,tr.TranDate,ISNULL(tr.TranQty,0) as TranQty,tr.JobNum,ISNULL(tr.AssemblySeq,0) as AssemblySeq,ISNULL(tr.JobSeq,0) as JobSeq,tr.GetMtlEmpID_c as GetMtlEmp,job.CommentText as JobCommentText,job.PartDescription as JobPartDesc
from dbo.V_NoReqMtlQry a
left join U_PartQuery t on t.PartNum=a.PartNum
left join Live.dbo.PartTran tr on tr.PartNum=a.PartNum and tr.TranType='STK-MTL'
left join Live.Erp.JobHead job on job.Company=tr.Company and job.JobNum=tr.JobNum

GO
/****** Object:  View [dbo].[V_MoreSugg]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[V_MoreSugg]
AS

with ReqGroup as (
	select t.PartNum,SUM(t.RequiredQty) as RequiredQtySum from [dbo].[V_NoReqMtlQry_1] t where t.ReqReleased=1 group by t.PartNum
),
SuppGroup as (
	select t.PartNum,SUM(t.SupplyQty) as SupplyQtySum from [dbo].[V_NoReqMtlQry_2] t group by t.PartNum
),
MtlSug as (
	select PartNum,Sum(SupplyQty) as SugQtySum from (
	select PartNum,LineDesc as PartDescription,RelQty as SupplyQty,PONUM,POLine,0 as PORelNum,0 as DocUnitCost,0 as MtlCost
	,'' as WarehouseCode,'' as BinNum,'' as LotNum
	,JobNum,AssemblySeq,JobSeq,
	(case when AssemblySeq>0 then 'STOM' else 'STOS' end) as SType, 'Sugg' as MtlS
	from Live.Erp.SugPoDtl where ReqNum=0 and SugType='M'
	) tt  group by tt.PartNum
)
select * from (
select ReqGroup.*,b.SupplyQtySum,b.SupplyQtySum-ReqGroup.RequiredQtySum as SQty,MtlSug.SugQtySum from ReqGroup
inner join SuppGroup b on b.PartNum=ReqGroup.PartNum
left join MtlSug on MtlSug.PartNum=ReqGroup.PartNum
) t where t.SQty>=0 and SugQtySum>0

GO
/****** Object:  View [dbo].[V_MoreSugg_2]    Script Date: 2019/7/19 14:00:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




CREATE VIEW [dbo].[V_MoreSugg_2]
AS

with ReqGroup as (
	select t.PartNum,SUM(t.RequiredQty) as RequiredQtySum,MIN(ptp.MinOrderQty) as MinOrderQty from [dbo].[V_NoReqMtlQry_1] t 
	left join Live.Erp.PartPlant ptp on ptp.Company=t.Company and ptp.PartNum=t.PartNum 
	where t.ReqReleased=1 group by t.PartNum
),
SuppGroup as (
	select t.PartNum,SUM(t.SupplyQty) as SupplyQtySum from [dbo].[V_NoReqMtlQry_2] t group by t.PartNum
),
MtlSug as (
	select PartNum,Sum(SupplyQty) as SugQtySum from (
	select SugPoDtl.PartNum,LineDesc as PartDescription,RelQty as SupplyQty,PONUM,POLine,0 as PORelNum,0 as DocUnitCost,0 as MtlCost
	,'' as WarehouseCode,'' as BinNum,'' as LotNum
	,JobNum,AssemblySeq,JobSeq,
	(case when AssemblySeq>0 then 'STOM' else 'STOS' end) as SType, 'Sugg' as MtlS
	from Live.Erp.SugPoDtl 
	where ReqNum=0 and SugType='M'
	) tt  group by tt.PartNum
)
select *,SugQtySum+t.SQtyX as MoreQty from (
	select *,
		(case when -a.SQty>=MinOrderQty then a.SQty else -MinOrderQty end) as SQtyX ---比较需求和最小起订量，需求大于等于最小起订量则用需求量，需求小于最小起订量则用最小起订量
	 from (
		select ReqGroup.*,b.SupplyQtySum,b.SupplyQtySum-ReqGroup.RequiredQtySum as SQty,MtlSug.SugQtySum from ReqGroup
		inner join SuppGroup b on b.PartNum=ReqGroup.PartNum
		left join MtlSug on MtlSug.PartNum=ReqGroup.PartNum
	) a where a.SQty<0 and SugQtySum+a.SQty>0
) t where SugQtySum+t.SQtyX>0
GO
